const router = require('koa-router')()
router.prefix('/model')
const service = require('../services/es.service')

const cfg = require('../config')
let sqlserver = require('mssql');
let dbConfigSource = {
    server: cfg.dicServer,
    database: 'MRFSQuery',
    user: 'sa',
    password: 'Docimax@123',
    port: 1433
};
function getIdString(pre) {
    let id = pre;
    let s = new Date();
    id +=
        s.getFullYear().toString() +
        s.getMonth().toString() +
        s.getDate().toString() +
        s.getHours().toString();
    id +=
        s.getMinutes().toString() +
        s.getSeconds().toString() +
        s.getMilliseconds().toString() +
        Math.round(Math.random() * 1000).toString();
    const tsleep = (time) => {
        return new Promise(resolve => setTimeout(resolve, time))
    }
    tsleep(1)
    return id.slice(0, 25);
}
async function querySql(strsql) {
    try {
        console.log('query sql:' + strsql);
        //let pool = await getConnection()
        var pool = new sqlserver.ConnectionPool(dbConfigSource);
        await pool.connect();
        let request = pool.request();
        request.multiple = true;
        let result = await request.query(strsql);

        console.log('query finished');
        console.log(result.recordset);
        return result.recordset
    } catch (error) {
        console.log(error);
    } finally {
        //await doRelease(pool);
        await sqlserver.close();
    }
}

function parseUrl(url) {

    let obj = {}
    let ind = url.indexOf('?')
    let u = decodeURI(url.substring(ind + 1))
    let arr = u.split('&')

    arr.forEach((e, i) => {
        let nameseperatepos = e.indexOf('=')
        let name = e.substr(0, nameseperatepos)
        let value = e.substr(nameseperatepos + 1, e.length - nameseperatepos - 1)
        obj[name] = (value)
    });

    return obj
}

router.get('/', async (ctx, next) => {
    //parameter dictable&diccolumns
    let request = ctx.request


    let p = parseUrl(ctx.request.url)
    console.log("parse finished");
    console.log(p);
    try {
        var r = []
        let id = p['id']
        let userid = p['userid']
        let limit = p['limit']//max count of module items per page
        let moreTime = p['moreTime']
        let queryUsername = p['queryUsername']
        let queryName = p['queryName']
        // let to = p['to']
        let sql = ""
        if (limit === undefined || limit <= 0)
            sql = "select * from table_model where 1=1"
        else
            sql = "select top " + limit + " * from table_model where 1=1"
        if (userid !== undefined && userid !== null && userid !== '')
            sql += " and (owner='" + userid + "' or shared=1)"
        if (id !== undefined && id !== null && id !== '')
            sql += " and id='" + id + "'"

        if (moreTime !== undefined && moreTime !== null && moreTime !== '')
            sql += " and createTime>='" + moreTime + "'"
        if (queryUsername !== undefined && queryUsername !== null && queryUsername !== '')
            sql += " and owner  like '%" + queryUsername + "%'"
        if (queryName !== undefined && queryName !== null && queryName !== '')
            sql += " and name like '%" + queryName + "%'"
        console.log(sql);
        r = await querySql(sql)
        console.log(r.length);
        sql += " order by lastexecutetime desc"

        ctx.body = {
            title: 'Success!',
            body: r
        }
    } catch (err) {
        console.log(err);
    }
})
router.get('/execute', async (ctx, next) => {
    //parameter dictable&diccolumns
    let request = ctx.request


    let p = parseUrl(ctx.request.url)
    console.log("parse finished");
    console.log(p);
    try {
        var r = []
        let id = p['id']
        if (id === undefined || id === '') {
            ctx.body = {
                title: 'Success!',
                body: r
            }
            return
        }
        //let userid = p['userid']
        if (id !== undefined && id !== null && id !== '') {
            let sql = "select * from table_model where id='" + id + "'"
            console.log(sql);

            r = await querySql(sql)
            let q = JSON.parse(cfg.decodeSingleQuote(r.dataset[0]['query']))
            if (q['type'] === 'query') {
                try {
                    let isMridMode = false
                    if (q['parameter'] != undefined && q['parameter']['isMridMode'] != undefined)
                        isMridMode = q['parameter']['isMridMode']
                    var r = await service.conditionsearch(cfg.index, q['conditionpipe'], q['showColumn'], 0, 30000000, 30000000, isMridMode)
                    ctx.body = {
                        title: 'Success!',
                        body: r
                    }
                } catch (err) {
                    console.log(err);
                }
            }
            if (q['type'] === 'aggregation') {
                try {
                    let isMridMode = false
                    if (q['parameter'] != undefined && q['parameter']['isMridMode'] != undefined)
                        isMeidMode = q['parameter']['isMridMode']
                    var r = await service.aggrigate(cfg.index, aggrigate)
                    ctx.body = {
                        title: 'Success!',
                        body: r
                    }
                } catch (err) {
                    console.log(err);
                }
            }
            console.log(r);
        }

        ctx.body = {
            title: 'Success!',
            body: r
        }
    } catch (err) {
        console.log(err);
    }
})
router.post('/create', async (ctx, next) => {
    //parameter dictable&diccolumns
    let request = ctx.request
    let p = ctx.request.body //获取post提交的数据
    try {
        let model = JSON.parse(p['model'])
        let userid = p['userid']
        if (model === undefined || model === null || userid === undefined || userid === null) {
            ctx.body = {
                title: 'missing information!',
                body: null
            }
            console.log('model is undefined');
            return
        }
        if (model['owner'] !== userid) {
            ctx.body = {
                title: 'owner check failed!',
                body: null
            }
            console.log('owner is undefined');
            return
        }
        let id = model['id']
        if (id === undefined || id === null || id === '') {
            id = getIdString('model')
        }

        else {
            let deleteSql = "delete from Table_model where  id='" + id + "'"
            await querySql(deleteSql)

        }
        //console.log(id);
        let sql = `insert into table_model 
    (ID,Name,Shared,Grade,SharedUser,Query,CreateTime,Owner,Type,Description)
     values
     ('`+ id + "','" + model['name'] + "','" + model['shared'] + "','" + model['grade'] + "','" + model['sharedUser'] + "','" + cfg.codeSingleQuote(model['query']) + "','" + (new Date()).toISOString() + "','" + model['owner'] + "','" + model['type'] + "','" + model['description'] + "')";
        let r = await querySql(sql)
        ctx.body = {
            title: 'Success!',
            body: r
        }
    } catch (err) {
        console.log(err);
    }
})

router.post('/delete', async (ctx, next) => {
    //parameter dictable&diccolumns
    let request = ctx.request
    let p = ctx.request.body //获取post提交的数据

    //let p = parseUrl(ctx.request.url)
    console.log("parse finished");
    console.log(p);
    try {
        let model = JSON.parse(p['model'])
        let userid = p['userid']
        if (model === undefined || model === null || userid === undefined || userid === null) {
            ctx.body = {
                title: 'missing information!',
                body: null
            }
            console.log('model is undefined');
            return
        }
        if (model['owner'] !== userid) {
            ctx.body = {
                title: 'owner check failed!',
                body: null
            }
            console.log('owner is undefined');
            return
        }
        let id = model['id']
        if (id === undefined || id === null || id === '') {
            ctx.body = {
                title: 'missing model id!',
                body: null
            }
            console.log('id is undefined');
            return
        }


        let deleteSql = "delete from Table_model where  id='" + id + "'"
        await querySql(deleteSql)



    } catch (err) {
        console.log(err);
    }
})

module.exports = router